package server;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import Client.Client;
import Client.Doctor;
import Client.Government;
import Client.Nurse;
import Client.PasswordGenerator;
import Client.Patient;

/**
 * Database is a class that specifies the journal database. Uses JDBC and the
 * MySQL Connector/J driver.
 */
public class SQLDatabase {

	/**
	 * The database connection.
	 */
	private Connection conn;

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later.
	 */
	public SQLDatabase() {
		conn = null;
	}

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 * 
	 * @param userName
	 *            The user name.
	 * @param password
	 *            The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection(String userName, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://puccini.cs.lth.se/" + userName, userName,
					password);
		} catch (SQLException e) {
			e.printStackTrace();

			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
		}
		conn = null;
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	/**
	 * An SQL prepared-statement object.
	 */
	private PreparedStatement ps = null;

	private String findJournal = "SELECT * FROM Journals WHERE patientNbr = ?";
	private String findPassword = "SELECT password FROM Clients WHERE personNbr = ?";
	private String findSalt = "SELECT salt FROM Clients WHERE personNbr = ?";
	private String findJournalById = "SELECT * FROM Journals WHERE id = ?";
	private String saveModification = "INSERT into Logs VALUES(0, ?, ?, ?)";
	private String insertJournal = "INSERT into  Journals VALUES(0, ?, ?, ?, ?, ?)";
	private String deleteJournal = "DELETE from Journals WHERE id = ?";
	private String findClient = "SELECT * FROM Clients WHERE personNbr = ?";
	private String insertClient = "INSERT into Clients VALUES(?, ?, ?, ?, ?)";

	public boolean modifyJournal(int journalId, String user, String text) {
		try {
			if (checkPrivileges(journalId, user).equals("x")
					|| checkPrivileges(journalId, user).equals("rw")) {
				// try {
				// ps = conn.prepareStatement(findJournalById);
				// ps.setInt(1, journalId);
				// // ResultSet rs = ps.executeQuery();
				// // while (rs.next()) {
				// // ps.setString(5, text);
				// // ps.executeUpdate();
				// // }
				// // update.setString();
				// // ps.setString(2, "USD");
				// } catch (SQLException e) {
				// System.out.println("modifyJournal fail");
				// e.printStackTrace();
				// }
				saveToLog(user, "modified journal-nbr: " + journalId);
				return true;
			}
			saveToLog(user, "tried to modified journal-nbr: " + journalId);
			return false;
		} catch (Exception e) {
			saveToLog(user, "tried to modified journal-nbr: " + journalId);
			return false;
		}
	}

	private String checkPrivileges(int journalNbr, String user) {
		String result = "-1";
		Journal journal = getJournalObject(journalNbr, user);
		Client cli = getClient(user);
		if (checkGetJournalAuthority(journal, user)) {
			if (cli.getID().equals(journal.getDoctor())
					|| cli.getID().equals(journal.getNurse())) {
				result = "rw";
			} else if (cli.getDivision() == journal.getDivision()) {
				result = "r";
			} else if (cli.getAuthority() == 0) {
				result = "x";
			} else if (cli.equals(journal.getPatient())) {
				result = "r";
			}
		}
		return result;
	}

	/** DONE AND DONE */
	private String getPassword(String user) {
		String pass = null;
		try {
			ps = conn.prepareStatement(findPassword);
			ps.setString(1, user);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				pass = rs.getString("password");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pass;
	}

	/** DONE AND DONE */
	private String getSalt(String user) {
		String pass = null;
		try {
			ps = conn.prepareStatement(findSalt);
			ps.setString(1, user);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				pass = rs.getString("salt");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pass;
	}

	/** DONE AND DONE */
	public String getJournal(String patientId, String user) {
		Journal j = null;
		ArrayList<Journal> list = new ArrayList<Journal>();
		StringBuilder sb = new StringBuilder();
		Patient p;
		Nurse n;
		Doctor d;
		String t;
		int div;
		try {
			ps = conn.prepareStatement(findJournal);
			ps.setString(1, patientId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				p = (Patient) getClient(rs.getString("patientNbr"));
				n = (Nurse) getClient(rs.getString("nurseNbr"));
				d = (Doctor) getClient(rs.getString("doctorNbr"));
				div = rs.getInt("division");
				t = rs.getString("data");
				j = new Journal(p, n, d, div, t);
				list.add(j);
			}
		} catch (Exception e) {
			saveToLog(user, "tried to get journal for patient:" + patientId);
			return ("fel");
		}
		try {
			if (checkGetJournalAuthority(j, user)) {
				int i = 0;
				while (list.size() > i) {
					sb.append("Patient: " + list.get(i).getPatient()
							+ " \nNurse: " + list.get(i).getNurse()
							+ " \nDoctor: " + list.get(i).getDoctor()
							+ " \nDivision: " + list.get(i).getDivision()
							+ " \nData: " + list.get(i).getData() + "\n");
					sb.append("\n");
					i++;
				}
				saveToLog(user, "fetched journal for patient: " + patientId);
				return sb.toString();
			}
		} catch (Exception e) {
			saveToLog(user, "tried to get journal for patient:" + patientId);
			return "fel";
		}
		saveToLog(user, "tried to get journal for patient:" + patientId);
		return "fel";
	}

	private Journal getJournalObject(int journalId, String user) {
		Journal j = null;
		Patient p;
		Nurse n;
		Doctor d;
		String t;
		int div;
		try {
			ps = conn.prepareStatement(findJournalById);
			ps.setInt(1, journalId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				p = (Patient) getClient(rs.getString("patientNbr"));
				n = (Nurse) getClient(rs.getString("nurseNbr"));
				d = (Doctor) getClient(rs.getString("doctorNbr"));
				div = rs.getInt("division");
				t = rs.getString("data");
				j = new Journal(p, n, d, div, t);
			}
		} catch (SQLException e) {
			System.out.println("getJournal fail");
			e.printStackTrace();
		}
		return j;
	}

	private boolean checkGetJournalAuthority(Journal j, String user) {
		boolean res = false;
		Client c1 = getClient(user);
		int auth = c1.getAuthority();
		if (auth == 0) {
			res = true;
		} else if (auth == 1) {
			if (j.getDivision() == c1.getDivision()
					|| j.getDoctor().equals(c1.getID())) {
				res = true;
			}

		} else if (auth == 2) {
			if (j.getDivision() == c1.getDivision()
					|| j.getNurse().equals(c1.getID())) {
				res = true;
			}

		} else {
			if (j.getPatient().equals(c1.getID())) {
				res = true;
			}
		}
		return res;
	}

	/** DONE AND DONE */
	public boolean insertJournal(String personNbr, String nurseNbr,
			String doctorNbr, int division, String data, String user) {

		if (getClient(user).getAuthority() == 1 && clientExists(nurseNbr)
				&& clientExists(doctorNbr) && clientExists(personNbr)) {
			try {
				ps = conn.prepareStatement(insertJournal);
				ps.setString(1, personNbr);
				ps.setString(2, nurseNbr);
				ps.setString(3, doctorNbr);
				ps.setInt(4, division);
				ps.setString(5, data);
				ps.executeUpdate();
				saveToLog(user, "added journal for patient: " + personNbr);
				return true;
			} catch (SQLException e) {
				saveToLog(user, "tried to add Journal for patient: "
						+ personNbr);
				e.printStackTrace();
			}
		}
		return false;
	}

	/** DONE AND DONE */
	public boolean saveToLog(String author, String text) {

		Calendar currentDate = Calendar.getInstance();
		SimpleDateFormat formatter = new SimpleDateFormat(
				"yyyy/MMM/dd HH:mm:ss");
		String dateNow = formatter.format(currentDate.getTime());
		try {
			ps = conn.prepareStatement(saveModification);
			ps.setString(1, author);
			ps.setString(2, text);
			ps.setString(3, dateNow);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			System.out.println("saveToLogFail");
			e.printStackTrace();
			return false;
		}
	}

	/** DONE AND DONE */
	public boolean insertClient(int type, String id, int division,
			String password) {
		String hashedPw;
		String salt;
		PasswordGenerator pg = new PasswordGenerator();
		salt = pg.newSalt();
		hashedPw = pg.newPassword(pg.saltPassword(password, salt));
		try {
			ps = conn.prepareStatement(insertClient);
			ps.setString(1, id);
			ps.setInt(2, division);
			ps.setInt(3, type);
			ps.setString(4, hashedPw);
			ps.setString(5, salt);
			ps.executeUpdate();
			saveToLog("System Administrator", "Added Client");
			return true;
		} catch (SQLException e) {
			System.out.println("insertClient Fail!");
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * DONE AND DONE Returns NULL if no Client with chosen id exists
	 */
	private Client getClient(String id) {
		int type = -1;
		int division = -1;
		if (clientExists(id)) {
			String password = null;
			try {
				ps = conn.prepareStatement(findClient);
				ps.setString(1, id);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					division = rs.getInt("division");
					type = rs.getInt("type");
					password = rs.getString("password");
				}
			} catch (SQLException e) {
				System.out.println("getClientFail");
				e.printStackTrace();
			}
			if (type == 0)
				return new Government(id, division, password);
			if (type == 1)
				return new Doctor(id, division, password);
			if (type == 2)
				return new Nurse(id, division, password);
			return new Patient(id, division, password);
		}
		return null;
	}

	/** DONE AND DONE */
	public boolean deleteJournal(String id, String user) {
		boolean deleted = false;
		if (getClient(user).getAuthority() == 0) {
			try {
				ps = conn.prepareStatement(findClient);
				ps.setString(1, user);
				ps.executeQuery();
				ps = conn.prepareStatement(deleteJournal);
				ps.setString(1, id);
				ps.executeUpdate();
				saveToLog(user, "Deleted Journal");
				deleted = true;
				saveToLog(user, "has deleted journal-nbr: " + id);
			} catch (SQLException e) {
				saveToLog(user, "has tried to delete journal-nbr: " + id);
				e.printStackTrace();
			}
		}
		return deleted;
	}

	/** DONE AND DONE */
	public boolean clientExists(String personNbr) {
		try {
			ps = conn.prepareStatement(findClient);
			ps.setString(1, personNbr);
			ResultSet rs = ps.executeQuery();
			return rs.next();
		} catch (SQLException e) {
			System.out.println("clientExists Fail");
			e.printStackTrace();
		}
		return false;
	}

	public boolean tryLogin(String user, String pw) {
		boolean loggedIn = false;
		PasswordGenerator pg;
		String salt;
		String hash;
		pg = new PasswordGenerator();

		String s;
		salt = getSalt(user);
		hash = pg.reHashPw(pg.saltPassword(pw, salt));
		try {
			if (getPassword(user).equals(hash)) {
				loggedIn = true;
				s = "Successful login";
			} else {
				s = "Unsuccessful login";
			}
		} catch (NullPointerException e) {
			s = "A client not found in database have tried to login";
		}
		saveToLog(user, s);
		System.out.println(s);
		return loggedIn;
	}
	/**
	 * AUTHORITY CHECKS FROM HERE! - V - V - V - V - V - V - V - V - V - V - V -
	 * V - V -
	 * 
	 * AUTHOR: DAVE THE FULHAxXx DOUSCHE!!!!
	 */

}
